package com.hs.hlw.credit.system.common.util;

import com.google.gson.JsonArray;
import com.hs.hlw.credit.system.common.enumeration.ExcelTypeEnum;
import com.hualongdata.exception.BusinessException;
import lombok.Data;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * ExcelXlsxUtil
 *
 * @author
 * @date 2018/05/07
 */
public class ExcelUtil {
    private static final Logger LOGGER = LoggerFactory.getLogger(ExcelUtil.class);

    /**
     * 获取行数
     *
     * @param filePath
     * @param type
     * @return
     */
    public static int getRowCount(String filePath, ExcelTypeEnum type) throws Exception {
        InputStream in = null;
        Workbook wb = null;
        try {
            in = new FileInputStream(filePath);
            wb = getWorkbook(in, type);

            // 获取第一个sheet
            Sheet sheet = wb.getSheetAt(0);

            // 获取最大行数
            int maxRow = sheet.getPhysicalNumberOfRows();

            return maxRow;
        } catch (Exception e) {
            throw e;
        } finally {
            close(wb, in);
        }
    }

    /**
     * 读取excel
     * @param filePath
     * @param type
     * @param columns
     * @param startRow
     * @return
     * @throws Exception
     */
    public static JsonArray readExcel(String filePath, ExcelTypeEnum type, String[] columns, int startRow) throws Exception {
        InputStream in = new FileInputStream(filePath);
        return readExcel(in, type, columns, startRow);
    }

    /**
     * 读取excel
     * @param fileBytes
     * @param type
     * @param columns
     * @param startRow
     * @return
     * @throws Exception
     */
    public static JsonArray readExcel(byte[] fileBytes, ExcelTypeEnum type, String[] columns, int startRow) throws BusinessException {
        return readExcel(new ByteArrayInputStream(fileBytes), type, columns, startRow);
    }

    /**
     * 读取excel
     * @param in
     * @param type
     * @param columns
     * @param startRow
     * @return
     * @throws Exception
     */
    public static JsonArray readExcel(InputStream in, ExcelTypeEnum type, String[] columns, int startRow) throws BusinessException {
        Workbook wb = null;
        try {
            wb = getWorkbook(in, type);

            // 获取第一个sheet
            Sheet sheet = wb.getSheetAt(0);

            // 获取最大行数
            int maxRow = sheet.getPhysicalNumberOfRows();

            // 解析数据
            List<Map<String, Object>> dataList = new ArrayList<>();
            for (int row = startRow; row < maxRow; row++) {
                Row rowData = sheet.getRow(row);
                Map<String, Object> map = new HashMap<>();
                for (int col = 0; col < columns.length; col++) {
                    if (rowData == null) {
                       continue;
                    }
                    map.put(columns[col], getCellFormatValue(rowData.getCell(col)));
                }
                dataList.add(map);
            }
            return JsonUtil.strToJsonArray(JsonUtil.toJson(dataList));
        } catch (Exception e) {
            throw new BusinessException("文件异常，请确认后再试");
        } finally {
            close(wb, in);
        }
    }

    /**
     * 获取单元格的值
     *
     * @param cell
     * @return
     */
    public static Object getCellFormatValue(Cell cell) {
        if (cell == null) {
            return null;
        }
        Object cellValue = null;
        if (CellType.NUMERIC.equals(cell.getCellTypeEnum())) {
            double num = cell.getNumericCellValue();
            int _num = (int) num;
            if (num == _num) {
                cellValue = String.valueOf(_num);
            } else {
                cellValue = String.valueOf(num);
            }
        } else if (CellType.FORMULA.equals(cell.getCellTypeEnum())) {
            //判断cell是否为日期格式
            if (DateUtil.isCellDateFormatted(cell)) {
                //转换为日期格式YYYY-mm-dd
                cellValue = cell.getDateCellValue();
            } else {
                //数字
                cellValue = String.valueOf(cell.getNumericCellValue());
            }
        } else if (CellType.STRING.equals(cell.getCellTypeEnum())) {
            cellValue = cell.getRichStringCellValue().getString();
        }
        return cellValue;
    }

    /**
     * 获取Workbook
     *
     * @param in
     * @param type
     * @return
     * @throws Exception
     */
    public static Workbook getWorkbook(InputStream in, ExcelTypeEnum type) throws Exception {
        Workbook wb = null;
        try {
            if (ExcelTypeEnum.XLS.equals(type)) {
                wb = new HSSFWorkbook(in);
            } else if (ExcelTypeEnum.XLSX.equals(type)) {
                wb = new XSSFWorkbook(in);
            } else {
                throw new BusinessException("excel文件类型指定错误");
            }
            return wb;
        } catch (Exception e) {
            throw e;
        }
    }

    /**
     * 关闭
     *
     * @param wb
     * @param in
     */
    public static void close(Workbook wb, InputStream in) {
        try {
            if (wb != null) {
                wb.close();
            }
            if (in != null) {
                in.close();
            }
        } catch (Exception e) {
            // TODO
        }
    }

    public static void main(String[] args) throws Exception {
        try {
            String[] columns = new String[]{"f1", "f2", "f3", "f4", "f4", "f6", "f7", "f8"};
            ExcelUtil.readExcel("D:\\file\\962df09d-a350-43d0-a508-266220d60eef.xls", ExcelTypeEnum.XLS, columns, 1);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
